﻿--go 

--exec sp_getDailySumData '2015-03-20'

CREATE  proc [dbo].[sp_getDailySumData](@StartDate as datetime)
as 
begin 
	declare @endDate datetime 
	select @endDate = GETDATE()
  
	if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
	drop table #dateTable 

	create table #dateTable ([date] varchar(10))        

	declare @dtTemp as datetime 
	select @dtTemp = @startDate   
	while (@dtTemp <= @endDate)
	begin 
	insert into #dateTable([date]) select Convert(varchar,@dtTemp,23)
	select @dtTemp =DATEADD(day,1, @dtTemp)
	end 


    select t1.[date],t10.userCnt, t1.bindCnt,t2.apptCnt,t3.billCnt,(t2.apptCnt + t3.billCnt)as NumberofTrades,(t2.apptMoney + t3.prescMoney - t3.yibaoMoney + t4.prepayMoney + t5.dinnerMoney) as daliyMoney,t6.ApptMoney + t7.prescMoney - t7.yibaoMoney + t8.prepayMoney + t9.dinnerMoney as sumMoney  from 
    
    -- USER 
    (select a.[date],coalesce(b.userCnt,0) as userCnt from #dateTable a left join 
	(select CONVERT(varchar,SubscribeDt,23) as [date] ,COUNT(*) as USERCnt 
	from [User] where SubscribeDt between  @startDate and  @endDate 
	GROUP by CONVERT(varchar,SubscribeDt,23)) b 
	on a.[date] = b.[date]) t10 ,
	
    
    --bind card 
    (select a.[date],coalesce(b.bindCnt,0) as bindCnt from #dateTable a left join 
	(select CONVERT(varchar,AddDt,23) as [date] ,COUNT(*) as bindCnt 
	from HospCard where AddDt between  @startDate and  @endDate 
	GROUP by CONVERT(varchar,AddDt,23)) b 
	on a.[date] = b.[date]) t1 ,

	--appt order 
	(select a.[date],coalesce(b.apptCnt,0) as apptCnt,coalesce(b.apptMoney,0) as apptMoney from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date],COUNT(*) as apptCnt,SUM(sumFee) as ApptMoney
	from ApptOrder where paystate =1  and  orderstatus = 21  and fetchtime between  @startDate and  @endDate 
	group by  Convert(varchar,fetchtime,23)) b 
	on a.[date] = b.[date]) t2,
	
	--bill order 
	(select a.[date],coalesce(b.prescMoney,0) as prescMoney,coalesce(b.yibaoMoney,0) as yibaoMoney ,coalesce(b.billCnt,0) as billCnt from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date] ,sum(PrescMoney) as  prescMoney , COUNT(*) as billCnt,
	SUM(case when yibaojydjh is null then 0 else yibaoMoney end ) as yibaoMoney
	from BillOrder where PayState = 1 and OrderStatus = 8  and fetchtime between  @startDate and  @endDate   
	group by Convert(varchar,fetchtime,23) ) b 
	on a.[date] = b.[date]) t3,

	--prepay order
	(select a.[date],coalesce(b.PrepayMoney,0) as prepayMoney from #dateTable a left join 
	(select Convert(varchar,fetchtime,23) [date] ,sum(PrepayMoney) as  PrepayMoney  
	from InpatientPrepayOrder where PayState = 1 and OrderStatus = 8  and fetchtime between  @startDate and  @endDate   
	group by Convert(varchar,fetchtime,23) ) b 
	on a.[date] = b.[date]) t4,		
	
	--dinner order 
	(select a.[date],coalesce(b.dinnerMoney,0) as dinnerMoney from #dateTable a left join 
	(select Convert(varchar,PayTime,23) [date] ,sum(TotalMoney) as  dinnerMoney  
	from dinnerOrder where PayStatus = 1 and OrderStatus = 6  and paytime between  @startDate and  @endDate   
	group by Convert(varchar,paytime,23) ) b 
	on a.[date] = b.[date]) t5,		
	
	--sum 						
	(	 	
		select c.[date],coalesce(d.ApptMoney,0) as ApptMoney from #dateTable c left join  
		(
			select a.[date] as [date],SUM(coalesce(sumFee,0)) as ApptMoney 
			from #dateTable a ,ApptOrder b 
			where b.paystate =1  and  b.orderstatus = 21  and b.FetchTime < DATEADD(day,1,a.[date])
			group by a.[date]
		)d on c.[date] = d.[date]
	) t6,
	
	(
		select c.[date],coalesce(d.prescMoney,0) as prescMoney,coalesce(d.yibaoMoney,0) as yibaoMoney from #dateTable c left join  
		(
			select a.[date],SUM(coalesce(b.prescMoney,0)) as prescMoney ,SUM(case when b.yibaojydjh is null then 0 else b.yibaoMoney end ) as yibaoMoney
			from #dateTable a ,BillOrder b 	
			where b.paystate =1  and  b.orderstatus = 8  and b.FetchTime < DATEADD(day,1,a.[date])
			group by a.[date]	
		) d on c.[date] = d.[date]
	) t7,	
	
	(
		select c.[date],coalesce(d.prepayMoney,0) as prepayMoney from #dateTable c left join  
		(
			select a.[date],SUM(coalesce(prepayMoney,0)) as prepayMoney 
			from #dateTable a ,InpatientPrepayOrder b 	
			where b.paystate =1  and  b.orderstatus = 8  and b.FetchTime < DATEADD(day,1,a.[date])
			group by a.[date]	
		)d	on c.[date] = d.[date]
	)t8,	
	(
		select c.[date],coalesce(d.dinnerMoney,0) as dinnerMoney from #dateTable c left join  
		(
			select a.[date],SUM(coalesce(totalMoney,0)) as dinnerMoney 
			from #dateTable a ,DinnerOrder b 	
			where b.paystatus = '1'  and  b.orderstatus = 6  and b.paytime < DATEADD(day,1,a.[date])
			group by a.[date]	
		)d	on c.[date] = d.[date]
	)t9
		
	where t1.[date] = t2.[date] and t3.[date]= t2.[date] and t3.[date] = t4.[date] 
		and t5.[date] = t4.[date] and t5.[date] = t6.[date] and t6.[date] = t7.[date]
		and t8.[date] = t7.[date] and t8.[date] = t9.[date] and t9.[date]=t10.[date]
	
	order by 1

    --drop table 
	if exists(select * from tempdb..sysobjects where  name  like N'%#dateTable%' and type='U')
	drop table #dateTable 

end

--drop table aa 
--create table aa([Date]  datetime)

--insert into aa(Date)
--select '2015-01-01'
--union 
--select '2015-01-02'

		--select a.[date],SUM(coalesce(sumFee,0)) as ApptMoney 
		--from aa a ,ApptOrder b where b.paystate =1  and  b.orderstatus = 21  and b.FetchTime < DATEADD(day,1,a.[date])
		--group by a.[date]
				
				
		--select a.[date],SUM(coalesce(b.prescMoney,0)) as prescMoney ,SUM(case when b.yibaojydjh is null then 0 else b.yibaoMoney end ) as yibaoMoney
		--from aa a ,BillOrder b 	
		--where b.paystate =1  and  b.orderstatus = 8  and b.FetchTime < DATEADD(day,1,a.[date])
		--group by a.[date]
		
		--select a.[date],SUM(coalesce(prepayMoney,0)) as prepayMoney 
		--from aa a ,InpatientPrepayOrder b 	
		--where b.paystate =1  and  b.orderstatus = 8  and b.FetchTime < DATEADD(day,1,a.[date])
		--group by a.[date]
